package org.dong.samples.dao.Impl;

import java.util.List;
import org.dong.samples.dao.TeacherHibernateDAO;
import org.dong.samples.model.Subject;
import org.dong.samples.model.UserSubject;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.type.StringType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
@Transactional
public class TeacherhibernateDAOImpl extends BaseHibernateDAO<Subject> implements TeacherHibernateDAO {
	
	@Autowired
	private SessionFactory sessionFactory;
	
	public Session getSession(){
		return this.sessionFactory.getCurrentSession();
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllUserBySubject(Integer subId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select user.user_id as userId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where es.sub_id = :subId ");
		return this.createSqlQuery(String.valueOf(sql))
				.addScalar("userId", StringType.INSTANCE)
				.setInteger("subId", subId)
				.list();
	}

	@Override
	public void insetSubjectUsers(List<String> insertList, Integer subId) {
		String sql = " INSERT INTO exam_user_subject(user_id, sub_id) values(:userId, :subId) ";
		for(String us:insertList){
			getSession().createSQLQuery(sql).setString("userId", us).setInteger("subId", subId).executeUpdate();
		}
	}

	@Override
	public void deleteSubjectUsers(List<String> delList, Integer subId) {
		String sql = " DELETE FROM exam_user_subject WHERE user_id=:userId and sub_id=:subId ";
		for(String us:delList){
			getSession().createSQLQuery(sql).setString("userId", us).setInteger("subId", subId).executeUpdate();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllStudent() {
		String sql = " select user_id,user_name from exam_user";
		return this.createSqlQuery(sql).list();
	}


	@SuppressWarnings("unchecked")
	@Override
	public List<UserSubject> getUserBySubjectId(Integer subId) {
		String sql = " select user_id from exam_user where sub_id=:subId";
		return this.createSqlQuery(sql).setInteger("subId", subId).list();
	}

}
